package com.huatai.dataservice.dubbo;

import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.extension.toolkit.SqlRunner;
import lombok.extern.slf4j.Slf4j;
import org.apache.dubbo.config.annotation.DubboService;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.transaction.annotation.Transactional;

import java.sql.*;
import java.util.*;

/**
 * @author Lion
 * @date 2023/6/27  15:04
 */
@Slf4j
@DubboService
@Transactional
public class DataDisposeDubboImpl implements DataDisposeDubboAPI{

	@Value("${spring.datasource.druid.driver-class-name}")
	private String driver;
	@Value("${ht.datasource.user.url}")
	private String url;
	@Value("${ht.datasource.user.username}")
	private String username;
	@Value("${ht.datasource.user.password}")
	private String password;

	/**
	 * @author Lion
	 * @description 校验数据库中是否存在表，有则跳过  无则新建
	 * @param tableName
	 * @param jsonData
	 * @return boolean
	 */
	@Override
	public boolean existTable(String tableName, List<JSONObject> jsonData) {
		try {
			Class.forName(driver);
			Connection connection = DriverManager.getConnection(url,username,password);
			DatabaseMetaData meta = connection.getMetaData();
			ResultSet resultSet = meta.getTables("","",tableName,null);

			//判断表是否存在，如果存在则关闭连接，如果不存在则创建表
			if (!resultSet.next()){

				String sql = transfer(tableName,jsonData);
				connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE).execute(sql);
				connection.close();
			}else {
				connection.close();
				return false;
			}
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		}
		return true;
	}
	// 定义需要转义的MySQL保留字及其对应替换值（可扩展）
	private static final Map<String, String> RESERVED_WORDS = new HashMap<>();

	static {
		RESERVED_WORDS.put("loop", "`loop`");
		RESERVED_WORDS.put("range", "`range`");
		RESERVED_WORDS.put("require", "`require`");
		// 可在此继续添加其他保留字，如：
		// RESERVED_WORDS.put("group", "'group'");
		// RESERVED_WORDS.put("order", "'order'");
	}

	/**
	 * @author Lion
	 * @description 封装insert语句
	 * @param tableName
	 * @param object
	 * @param list
	 * @return java.lang.String
	 */
	@Override
	public String generateInsertStatement(String tableName, JSONObject object, List<String> list) {
		StringBuilder sb = new StringBuilder();
		StringBuilder sb2 = new StringBuilder();
		sb.append("INSERT INTO ");
		sb.append(tableName);
		sb.append(" (");

		sb2.append(") VALUES(");
		if (list != null) {
			int size = list.size();
			for (int i = 0; i < size; i++) {
				boolean b = false;
				Iterator<Map.Entry<String, String>> iterator = RESERVED_WORDS.entrySet().iterator();
				while (iterator.hasNext()) {
					Map.Entry<String, String> entry = iterator.next();
					String key = entry.getKey();
					String value = entry.getValue();
					if(list.get(i).equals(key)){
						sb.append(value);
						b = true;
					}
				}
				if (!b){
					sb.append(list.get(i));
				}
				sb2.append("'");
				sb2.append(object.get(list.get(i)));
				sb2.append("'");
				if (i != size - 1) {
					sb.append(",");
					sb2.append(",");
				}
			}
			sb2.append(");");
		}
		return sb.append(sb2).toString();
	}

	/**
	 * @author Lion
	 * @description 执行SQL语句
	 * @param  sql
	 * @return void
	 */
	@Transactional
	@Override
	public void insertDataToTable(String sql) {
		try {
			SqlRunner.db().insert(sql);
		}catch (Exception e){
			log.error(e.getMessage());
		}

	}

	/**
	 * @author Lion
	 * @description 获取表数据条数（实时的数据表名前缀为：“tb_rt_”，非实时的表名为：“tb_ht_”）
	 * @param
	 * @return java.util.List<com.alibaba.fastjson.JSONObject>
	 */
	@Override
	public List<JSONObject> getAllTableCount() {

		Connection connection = null;
		try {
			connection = DriverManager.getConnection(url,username,password);
		} catch (SQLException e) {
			e.printStackTrace();
		}

		List<JSONObject> list = new ArrayList<>();
		JSONObject jsonObject1 = new JSONObject();
		JSONObject jsonObject2 = new JSONObject();
		int dataCount = 0;
		int dataCount2 = 0;

		PreparedStatement pst = null;

		try {
			pst = connection.prepareStatement("SHOW TABLES");
			ResultSet resultSet = pst.executeQuery();

			while (resultSet.next()){
				String s = resultSet.getString(1);
				if (s.contains("tb_rt_")){
					int count =  resultSet.getRow();
					dataCount = dataCount + count;
				}else {
					int count2 = resultSet.getRow();
					dataCount2 = dataCount2 + count2;
				}
			}

			jsonObject1.put("实时数据量",dataCount);
			jsonObject2.put("http请求数据量",dataCount2);

			list.add(jsonObject1);
			list.add(jsonObject2);

			return list;

		} catch (Exception e) {
			log.error(e.getMessage(), e);
			return null;
		}finally {
			try {
				if (pst != null){
					pst.close();
				}
				if (connection != null){
					connection.close();
				}
			} catch (SQLException throwables) {
				throwables.printStackTrace();
			}
		}
	}


	public String transfer(String objectTableName, List<JSONObject> jsonData) throws SQLException {

		String fieldName;
		String fieldType;
		String fieldComments;

		StringBuilder start = new StringBuilder().append("CREATE TABLE ")
			.append(objectTableName).append("(");
		StringBuilder builder = new StringBuilder();

		Iterator<JSONObject> iterator = jsonData.iterator();
		while (iterator.hasNext()){

			JSONObject jsonObject = iterator.next();
			fieldName= jsonObject.getString("fieldName");
			fieldType= jsonObject.getString("fieldType");
			fieldComments= jsonObject.getString("fieldComments");
			builder.append("`");
			builder.append(fieldName).append("`").append(" ");

			//如果为时间类型  长度为3
			if ("datetime".equals(fieldType)){
				builder.append(fieldType).append(" ");
				//builder.append(fieldType).append("(").append(3).append(")").append(" ");
			}else if("varchar".equals(fieldType)){
				builder.append(fieldType).append("(").append(255).append(")").append(" ");
			}else {
				builder.append(fieldType).append(" ");
			}
			builder.append("NULL DEFAULT NULL ");
			builder.append("COMMENT").append(" ").append("'").append(fieldComments).append("'");
			builder.append(",");

		}
		builder.deleteCharAt(builder.length()-1);
		builder.append(")");
		return start.append(builder).toString();
	}
}
